﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace SnapObjects.Data.PostgreSql
{
    internal class PostgreSqlFunctionParameterSchemaProvider : RoutineParameterSchemaProvider
    {
        public PostgreSqlFunctionParameterSchemaProvider(DataContext dataContext) : base(dataContext)
        {
        }

        protected override IList<DynamicModel> QueryParameters(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var sql = this.GetSchemaSql(nameInfo, helper);

            return _dataContext.SqlExecutor.Select<DynamicModel>(sql);
        }

        protected override Task<IList<DynamicModel>> QueryParametersAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var sql = this.GetSchemaSql(nameInfo, helper);

            return _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sql, cancellationToken);
        }

        protected override ProcedureSchema CreateParameterSchema(
            string name, 
            IReadOnlyList<AdoDbParameter> adoDbParameters,
            IReadOnlyList<DynamicModel> dbParameters)
        {
            return new ProcedureSchema(
                name, adoDbParameters, FunctionReturnType.Table);
        }

        private string GetSchemaSql(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var databaseName = "current_catalog";
            var schemaName = "current_schema";

            var functionName = helper.RemoveDelimitIdentifier(nameInfo.Name);

            if (!String.IsNullOrWhiteSpace(nameInfo.Schema))
            {
                var schema = helper.RemoveDelimitIdentifier(nameInfo.Schema);

                schemaName = $"'{schema}'";
            }

            if (!String.IsNullOrWhiteSpace(nameInfo.Database))
            {
                var database = helper.RemoveDelimitIdentifier(nameInfo.Database);

                databaseName = $"'{database}'";
            }

            return this.GetSchemaQuerySql(databaseName, schemaName, functionName);
        }

        private string GetSchemaQuerySql(string databaseName, string schemaName, string name)
        {
               var sql = @"
SELECT proc.specific_name as object_uniqueidentifier,
       args.parameter_name as parameter_name,
       args.data_type as parameter_datatype,
       args.parameter_mode as parameter_mode,
       args.numeric_precision as parameter_precision,
       args.numeric_scale as parameter_scale,
       args.parameter_default as parameter_defaultvalue
  FROM information_schema.routines proc 
  LEFT JOIN information_schema.parameters args
    ON proc.specific_schema = args.specific_schema
   AND proc.specific_name = args.specific_name
   AND proc.specific_catalog = args.specific_catalog
 WHERE proc.routine_type = 'FUNCTION'
   AND lower(proc.specific_catalog) = lower({0})   
   AND lower(proc.specific_schema) = lower({1})
   AND lower(proc.routine_name) = lower('{2}') 
 ORDER BY proc.routine_type, proc.specific_name, args.ordinal_position";
            
            return String.Format(sql, databaseName, schemaName, name);
        }
    }
}
